package com.dp.service.impl3;

import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityNotFoundException;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pub.BaseBean;
import com.dp.pub.MyServer;
import com.dp.pub.PageJson;
import com.dp.pub.UtilsBean;
import com.dp.util.DataMap;
import com.dp.vo.TSpcode;

@Service
@Transactional
public class BaseImpl extends BaseBean{
	protected final Log log = LogFactory.getLog(getClass());
	@PersistenceContext(unitName = "mySQLVOPU")
	protected EntityManager em;

	 protected String getString(Object o)
     {             
		 return (o==null?"":o.toString());
			                                     
     }
	 protected Integer getInteger(Object o)
     {             
		 return (o==null?0:Integer.valueOf(o.toString()));
			                                     
     }
	 protected Double getDouble(Object o)
     {             
		 return (o==null?0:Double.valueOf(o.toString()));
			                                     
     }
	 
	 public String getOneString() throws Exception
     {             
    	 String r="";
		 List<Object> s=em.createNativeQuery(sql).getResultList();
		 if(s.size()!=0 && s.get(0)!=null)r=s.get(0).toString();
     	 return r;                                      
     }
	 public String getOneString(String sqls) throws Exception
     {             
    	 String r="";
		 List<Object> s=em.createNativeQuery(sqls).getResultList();
		 if(s.size()!=0 && s.get(0)!=null)r=s.get(0).toString();
     	 return r;                                      
     }
	 public int getOneInt()
     {             
    	 int r=0;
    	 List<Object> s=em.createNativeQuery(sql).getResultList();
		 if(s.size()!=0 && s.get(0)!=null)r=Integer.valueOf(s.get(0).toString());       
      return r;                                      
     }
	@SuppressWarnings("unchecked")
	public List getQueryList(String hql, int first, int max) {
		Query query = em.createQuery(hql);
		query.setFirstResult(first);
		query.setMaxResults(max);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List getQueryList(String hql) {
		Query query = em.createQuery(hql);
		return query.getResultList();
	}
	
	@SuppressWarnings("unchecked")
	public int updateHql(String hql) {
		Query query = em.createQuery(hql);
		return query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	public Object getSingleResult(String hql) {
		Query query = em.createQuery(hql);
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}

//	@SuppressWarnings("unchecked")
//	public List getNamedQuery(String namehql) {
//		Query query = em.createNamedQuery(namehql);
//		return query.getResultList();
//	}

	@SuppressWarnings("unchecked")
	public List getNativeQuery(String sql) {
		Query query = em.createNativeQuery(sql);
		return query.getResultList();
	}
	
	public int updateNativeQuery(String sql) {
		return em.createNativeQuery(sql).executeUpdate();
	}
	
	@SuppressWarnings("unchecked")
	public List getNativeQuery(String sql, int first, int max) {
		Query query = em.createNativeQuery(sql);
		query.setFirstResult(first);
		query.setMaxResults(max);
		return query.getResultList();
	}

	public Object save(Object o) {
		return this.em.merge(o);
	}
	
	public void persist(Object o) {
		this.em.persist(o);
	}
	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("unchecked")
	public Object get(Class clazz, Serializable id) {
		Object o = this.em.find(clazz, id);

		if (o == null) {
			String msg = "Uh oh, '" + clazz + "' object with id '" + id
					+ "' not found...";
			log.warn(msg);
			throw new EntityNotFoundException(msg);
		}

		return o;
	}

	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("unchecked")
	public List getAll(Class clazz) {
		return this.em.createQuery(
				"select obj from " + clazz.getSimpleName() + " obj")
				.getResultList();
	}

	/**
	 * {@inheritDoc}
	 */

	@SuppressWarnings("unchecked")
	public void remove(Class clazz, Serializable id) {
		this.em.remove(this.get(clazz, id));
	}
	
	@SuppressWarnings("unchecked")
	public void remove(Object object) {
	
		this.em.remove(object);
	}


	 /*
     * 实现处理单条SQL语句的操作
     * 该操作处理单条SQL语句，包括数据的增、删、改处理。
     * @parm   updateSql     SQL语句
     * @return 以boolean返回，判断操作是否成功
     */
    public boolean execUpdate(String updateSql) throws Exception {
        int iCode = 0;
        boolean resultState = true;
        
        try{
//        	this.init();       
//            iCode = stmt.executeUpdate(updateSql);
//            System.out.println("iCode:"+iCode);
//            conn.commit();
//            this.closeAll();
        	this.em.createNativeQuery(updateSql).executeUpdate();
        } catch (Exception e) {
            resultState = false;
            log.debug(
                "Error Occur.When Execute the Sql and Return Code " + iCode +
                " : " + updateSql);
            e.printStackTrace();
        } 
        //finally {this.closeAll(); }

        return resultState;
    }
    
    /*
     * 实现处理单条SQL语句的操作
     * 该操作处理单条SQL语句，包括数据的增、删、改处理。
     * @parm   updateSql     SQL语句
     * @return 以iCode返回，判断操作是否成功 值为受影响行数
     */
    public int execUpdateRicode(String updateSql) throws Exception {
        int iCode = 0;    
        try{
        	//this.init();       
            iCode = this.em.createNativeQuery(updateSql).executeUpdate();       
            //this.closeAll();
        } catch (Exception e) {
            log.debug(
                "Error Occur.When Execute the Sql and Return Code " + iCode +
                " : " + updateSql);
            e.printStackTrace();
        } 
        //finally {this.closeAll(); }
        return iCode;
    }
    /*
     * jdbc调存储过程
     * @parm   sql     SQL语句
     * @return 返回flag，判断操作是否成功
     */
//    public boolean execCall(String sql) throws Exception {
//    	boolean flag = true;
//        try{
//        	this.init();
//        	cstmt = conn.prepareCall(sql);
//        	cstmt.execute();
//        	this.closeAll();
//        } catch (java.sql.SQLException e) {
//            log.debug("Error Occur.When Execute the Sql and Return Code : " + sql);
//            flag = false;
//            e.printStackTrace();
//        }  finally {this.closeAll(); }
//        return flag;
//    }

    /*
     * 实现处理多条SQL事务
     * 该操作实现处理多条SQL事务，包括数据的增、删、改处理。
     * @parm   batchSql     SQL语句数组
     * @return 以boolean返回，判断操作是否成功
     */
    public boolean execTransaction(String[] batchSql) throws Exception {
        
        int[] iCode;
        boolean returnState = true;
        int i = 0;
        
        try{
        	//this.init();   

            for (; i < batchSql.length; i++) {
            	
            	//log.debug(batchSql[i]);
               // stmt.addBatch(batchSql[i]);
                this.em.createNativeQuery(batchSql[i]).executeUpdate();
            }

//            iCode = stmt.executeBatch();
//            conn.commit();
//            this.closeAll();
        } catch (Exception e) {
          //  conn.rollback();
            log.debug("Error Occur.When execut the sql : ");
            returnState = false;
            throw new Exception(e.getMessage());
        }
        //finally {this.closeAll(); }

        return returnState;
    }

    public boolean execTransaction(ArrayList batchSql)
        throws Exception {
       
        int[] iCode;
        boolean returnState = true;
        int i = 0;
        
        try{
//        	this.init();   
            

            for (; i < batchSql.size(); i++) {
                sql = UtilsBean.ObjectToString(batchSql.get(i));

                this.em.createNativeQuery(sql).executeUpdate();
                //log.debug("********"+sql);
               // stmt.addBatch(sql);
            }

//            iCode = stmt.executeBatch();
//            conn.commit();
//            this.closeAll();
        } catch (Exception e) {
          //  conn.rollback();
            log.debug("Error Occur.When execut the sql : "+sql);
            returnState = false;
            throw new Exception(e.getMessage());
        } 
        //finally {this.closeAll(); }

        return returnState;
    }

    /*
     * 实现分页查询操作
     * 该查询实现分页查询操作，通过指定参数，得到符合要求的数据段。
     * @parm beginNo      开始记录位置
     * @parm pageRowCount 记录数
     * @return 以Object数组对象返回的查询结果
     */
    public Object[] queryByResultSet(String querySql, int beginNo,
        int pageRowCount) throws Exception {
        Object[] returnObj = new Object[3];
        String[] columnName;
       
       // ResultSet Rs = null;
      
        ArrayList ListData = new ArrayList();
        int columnCount = 0;
        int recordCount = 0;
        int Count = 0;
        
        
        try{
        	String tm=querySql.toLowerCase();
        	tm=tm.substring(0,tm.indexOf("from"));
        	tm=tm.replace("select", "");
        	tm=tm.replace("distinct", "");
        	
        	String []ss=tm.split(",");
        //	System.out.println(tm);
        	List<String>cn=new ArrayList<String>();
        	for(String s:ss)
        	{
        		String t=s;
        		int i=s.indexOf("as");
        		if(i!=-1)t=t.substring(i+2,t.length());
        		t=t.replaceAll(" ", "");
        		i=t.indexOf(".");
        		if(i!=-1)t=t.substring(i+1,t.length());
//        		System.out.println(t);
//        		System.out.println(s.indexOf("."));
        		cn.add(t);
        	}
       
//            ResultSetMetaData rsmd = rs1.getMetaData();
//            int NumCols = rsmd.getColumnCount();
//            columnName = new String[NumCols];
//
//            for (int k = 0; k < NumCols; k++) {
//                columnName[k] = rsmd.getColumnName(k + 1).toString().toLowerCase();
//                if(columnName[k].equals(""))
//                	columnName[k] = rsmd.getColumnLabel(k + 1).toString().toLowerCase();
//                //log.debug("columnName="+columnName[k]);
//            }
//
//            rs1.last();
        	Query	query=em.createNativeQuery(querySql);
        	
          

            //该判断用于光标绝对定位,执行失败则打印信息
//            if (!rs1.absolute(beginNo)) {
//            	log.debug("The Cursor Absolute Failure !!! ");
//            }

          

            int NumFlag = 0;

            List<Object[]> tempp=query.getResultList();
            recordCount = tempp.size();
            Count = recordCount / pageRowCount;

            //该判断用于计算总页数         
            if ((recordCount % pageRowCount) != 0) {
                Count++;
            }

            //该判断用于计算最后一页的记录数
            if ((recordCount - (beginNo - 1)) < pageRowCount) {
                pageRowCount = recordCount - ((Count - 1) * pageRowCount);
            }
			for(Object[]o:tempp)
			{
               // int i = 0;
                DataMap hash = new DataMap();
                for(int i=0;i<ss.length;i++)
                	  hash.put(cn.get(i), this.getString(o[i]));

                ListData.add(hash);
                NumFlag++;
            }

//            log.debug(
//                "The Query Is Successful and The Begin Row Is = " + beginNo);
          
        } catch (Exception e) {
        	log.debug(
                "Error Occur.When Execute the Query Sql and the Begin Row is " +
                beginNo + " : " + querySql);
            e.printStackTrace();
        } 
        //该try块用于装载返回值 
        try {
            returnObj[0] = new Integer(recordCount); //总记录数
            returnObj[1] = new Integer(Count); //总页数
            returnObj[2] = ListData; //当前页数据
        } catch (Exception e) {
        	log.debug("Error Occur.When Loading the Query Data!!");
            e.printStackTrace();
        }

        return returnObj;
    }

    /*
     * 实现查询所有符合条件的操作
     * @Param querySql  查询语句
     * @return 以Object数组对象返回的查询结果
     */
    public ArrayList queryALLResultSet(String querySql)
        throws Exception {
    //    String[] columnName;
       // Statement stmt = null;
       
        //Connection conn = null;
        ArrayList ListData = new ArrayList();
     //   int columnCount = 0;

       // System.out.println()
        try{
        //	this.init();   
          //  rs1 = stmt.executeQuery(querySql);
        	
        	String tm=querySql.toLowerCase();
        	tm=tm.substring(0,tm.indexOf("from"));
        	tm=tm.replace("select", "");
        	tm=tm.replace("distinct", "");
        	String []ss=tm.split(",");
        //	System.out.println(tm);
        	List<String>cn=new ArrayList<String>();
        	for(String s:ss)
        	{
        		String t=s;
//        		int i=s.indexOf("as");
//        		if(i!=-1)t=t.substring(i+2,t.length());
//        		t=t.replaceAll(" ", "");
//        		i=t.indexOf(".");
//        		if(i!=-1)t=t.substring(i+1,t.length());
        		
        		
        		 int i = s.indexOf(" as ");
        		 /*  464 */         if (i != -1) t = t.substring(i + 2, t.length());
        		 /*  465 */         t = t.replaceAll(" ", "");
        		 /*  466 */         i = t.indexOf(".");
        		 /*  467 */         if (i != -1) t = t.substring(i + 1, t.length());
//        		System.out.println(t);
//        		System.out.println(s.indexOf("."));
        		cn.add(t);
        	}
        	
//        	 int NumCols = ss.length;
//             columnName = new String[NumCols];
//
//             for (int k = 0; k < NumCols; k++) {
//                 columnName[k] = rsmd.getColumnName(k + 1).toString().toLowerCase();
//                 if(columnName[k].equals(""))
//                 	columnName[k] = rsmd.getColumnLabel(k + 1).toString().toLowerCase();
//             }
        	
        	Query	query=em.createNativeQuery(querySql);
        	//emr.createNativeQuery(sqlString, resultClass)
        	//query.
        	
        	
            
        	
//            ResultSetMetaData rsmd = rs1.getMetaData();
//            int NumCols = rsmd.getColumnCount();
//            columnName = new String[NumCols];
//
//            for (int k = 0; k < NumCols; k++) {
//                columnName[k] = rsmd.getColumnName(k + 1).toString().toLowerCase();
//                if(columnName[k].equals(""))
//                	columnName[k] = rsmd.getColumnLabel(k + 1).toString().toLowerCase();
//            }

           // rs1.previous();

        	/*      */ 
        	/*  502 */       System.out.println("----------" + querySql);
        	/*      */ 
        	/*  504 */       Object temppa = query.getResultList();
        	/*      */ 
        	/*  506 */       System.out.println("----------" + temppa.getClass().getName());
        	/*      */ 
        	/*  513 */       if ((temppa instanceof ArrayList))
        	/*      */       {
        	/*  515 */         List tempp = (List)temppa;
        	/*  516 */         for (int a = 0; a < tempp.size(); a++)
        	/*      */         {
        	/*  518 */           Object o = tempp.get(a);
        	/*  519 */           DataMap hash = new DataMap();
        	/*  520 */           if ((o instanceof Object[]))
        	/*      */           {
        	/*  523 */             Object[] oo = (Object[])o;
        	/*  524 */             for (int i = 0; i < ss.length; i++) {
        	/*  525 */               hash.put(cn.get(i), getString(oo[i]));
        	/*      */             }
        	/*      */ 
        	/*      */           }
        	/*      */           else
        	/*      */           {
        	/*  533 */             for (int i = 0; i < ss.length; i++) {
        	/*  534 */               hash.put(cn.get(i), getString(o));
        	/*      */             }
        	/*      */ 
        	/*      */           }
        	/*      */ 
        	/*  541 */           ListData.add(hash);
        	/*      */         }
        	/*      */       }
        	/*      */       else {
        	/*  545 */         DataMap hash = new DataMap();
        	/*  546 */         for (int i = 0; i < ss.length; i++)
        	/*  547 */           hash.put(cn.get(i), getString(temppa));
        	/*  548 */         ListData.add(hash);
        	/*      */       }
        	
        	
        	
        	
        	
        	
        	
//            List<Object[]> tempp=query.getResultList();
//			for(Object[]o:tempp)
//			{
//               // int i = 0;
//                DataMap hash = new DataMap();
//                for(int i=0;i<ss.length;i++)
//                	  hash.put(cn.get(i), this.getString(o[i]));
////                for (; i < NumCols; i++) {
////                    String columnValue = rs1.getString(i + 1);
////                    columnValue = (columnValue == null) ? "" : columnValue;
////                    hash.put(columnName[i], columnValue);
////                }
//
//                ListData.add(hash);
//            }

//            log.debug("The Query Is Successful and The Begin Row Is ");
          //  this.closeAll();
        } catch (Exception e) {
            e.printStackTrace();
        } // finally {this.closeAll(); }
        return ListData;
    }

    /*
     * 实现查询所有符合条件的操作
     * @Param querySql  查询语句
     * @return 以Object数组对象返回的查询结果
     * 修改getColumnName为getColumnLabel 用字段别名作为map里的key
     */
//    public ArrayList queryALLResultSetByColumnLabel(String querySql)
//        throws Exception {
//        String[] columnName;
//        ArrayList ListData = new ArrayList();
//        int columnCount = 0;
//        try{
//        	this.init();   
//            rs1 = stmt.executeQuery(querySql);
//            ResultSetMetaData rsmd = rs1.getMetaData();
//            int NumCols = rsmd.getColumnCount();
//            columnName = new String[NumCols];
//            for (int k = 0; k < NumCols; k++) {
//                columnName[k] = rsmd.getColumnLabel(k + 1).toString().toLowerCase();
//            }
//            while (rs1.next()) {
//                int i = 0;
//                DataMap hash = new DataMap();
//                for (; i < NumCols; i++) {
//                    String columnValue = rs1.getString(i + 1);
//                    columnValue = (columnValue == null) ? "" : columnValue;
//                    hash.put(columnName[i], columnValue);
//                }
//                ListData.add(hash);
//            }
//            log.debug("The Query Is Successful and The Begin Row Is ");
//            this.closeAll();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }  finally {this.closeAll(); }
//        return ListData;
//    }
    
    /*
     * 实现单行查询操作
     * 该查询得到符合要求的一行数据。
     * @Param querySql  查询语句
     * @return 以DataMap对象返回的查询结果
     */
//    public DataMap queryOneRow(String querySql) throws Exception {
//        String[] columnName;
//       // Statement stmt = null;
//       // ResultSet rs = null;
//       // Connection conn = null;
//        DataMap hash = new DataMap();
//        int columnCount = 0;
//        
//        try{
//        	this.init();   
//            rs1 = stmt.executeQuery(querySql);
//
//            ResultSetMetaData rsmd = rs1.getMetaData();
//            int NumCols = rsmd.getColumnCount();
//            columnName = new String[NumCols];
//
//            for (int k = 0; k < NumCols; k++) {
//                columnName[k] = rsmd.getColumnName(k + 1).toString()
//                                    .toLowerCase();
//
//                //log.debug("columnName="+columnName[k]);
//            }
//
//            if (rs1.next()) {
//                int i = 0;
//
//                for (; i < NumCols; i++) {
//                    String columnValue = rs1.getString(i + 1);
//                    columnValue = (columnValue == null) ? "" : columnValue;
//                    hash.put(columnName[i], columnValue);
//                }
//
//                log.debug("The Query Is Successful !");
//            }
//            this.closeAll();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }  finally {this.closeAll(); }
//
//        return hash;
//    }

    /**
         * 实现单值查询操作
         * 该查询实现单值查询，比如符合条件的记录总数、符合条件的某个特定字段值等等
         * @param  sql  查询SQL语句。
         * @return 以String对象返回的查询结果
         */
    public String queryOne(String sql) throws Exception {
       // Statement stmt = null;
      return  this.getOneString(sql);
      //  Connection conn = null;
//        String returnValue = null;
//        
//        try{
//        	this.init();   
//            rs1 = stmt.executeQuery(sql);
//
//            if (rs1.next()) {
//                returnValue = rs1.getString(1);
//            }
//            this.closeAll();
//        } catch (Exception e) {
//        	log.debug("Error Occur.When Execute the Sql : " + sql);
//            e.printStackTrace();
//        }  finally {this.closeAll(); }
//        return returnValue;
    }

    /**
       * 实现是否存在该记录的查询操作
       * @param  sql  查询SQL语句。
       * @return 以boolean对象返回的查询结果
       */
    public boolean queryExist(String sql) throws SQLException {
    	List temp=this.em.createNativeQuery(sql).getResultList();
    	return temp.size()>0;
//        boolean returnValue = false;
//       // Statement stmt = null;
//       
//        
//        try{
//        	this.init();   
//            rs1 = stmt.executeQuery(sql);
//
//            if (rs1.next()) {
//                returnValue = true;
//            }
//            this.closeAll();
//        } catch (Exception e) {
//        	log.debug("Error Occur.When Execute the Sql : " + sql);
//            e.printStackTrace();
//        }  finally {this.closeAll(); }
//        return returnValue;
    }

    /**
      * 下拉列表生成器
      ×@param  selName    列表框的名（name）
      ×@param  sql        sql
      ×@param  ID        获得的VALUE名称
      ×@param  Name      获得列表的名称
      ×@param  flag      判断是否增加“所有”
      * @return 以String对象返回的生成好的列表
      */
    public String getList(String selName, String sql, String ID, String Name,
        boolean flag) {
        String res = null;
        String op = "";
        DataMap li = null;
        ArrayList lists = new ArrayList();

        try {
            lists = queryALLResultSet(sql);
        } catch (Exception e) {
        	log.debug("Error :" + e.getMessage());
        }

        for (int i = 0; i < lists.size(); i++) {
            li = (DataMap) lists.get(i);
            op += ("<option value='" + li.get(ID) + "'>" + li.get(Name) + "</option>");
        }

        if (!flag) {
            res = "<select size='1' name='" + selName + "'>" + op +
                "</select>";
        } else {
            res = "<select size='1' name='" + selName + "'>" +
                "<option value='' selected>所有</option>" + op + "</select>";
        }

        return res;
    }

    /**
     * 从页面得到信息，分析并得到相应的SQL语句
     ×@param  flag    当前状态，是否在“WHERE”后有操作
     ×@param  Data    页面返回的数值
     ×@param  strSql) 对应的判断语句
     *@return  返回符合要求的SQL
     */
    public String SelectSqlDate(boolean flag, String Data, String strSql) {
        String ResultSq1 = "";

        if ((Data != null) && (Data.length() > 0)) {
            if (flag) {
                ResultSq1 = " AND " + strSql;
            } else {
                ResultSq1 = " where " + strSql;
                flag = true;
            }

            log.debug("===Data===" + ResultSq1);
        }

        return ResultSq1;
    }

    /**
    * 从页面得到信息，分析并得到相应的SQL语句
    ×@param  Data    页面返回的数值
    ×@param  strSql) 对应的判断语句
    *@return  返回符合要求的SQL
    */
    public String JudgeSqlDate(String Data, String strSql) {
        String ResultSq1 = "";

        if ((Data != null) && (Data.length() > 0)) {
            ResultSq1 = " AND " + strSql;
            log.debug("===Data===" + ResultSq1);
        }

        return ResultSq1;
    }
    
    public String getSpcodeByUserId(String userid)
	{
		String sql = "select te.sp_code from t_employee te, t_user tu where tu.employee_id=te.employee_id and tu.user_id='" + userid + "'";
		String s = "";
		try {
			s = getOneString(sql);
		} catch (Exception e) {
			log.info(" getSpcodeByUserId excute sql : " +sql);
			log.info(" getSpcodeByUserId fail !!!" +e.getMessage());
			e.printStackTrace();
			return "nospcode";
		}
		return s;
	}
    
    /**
	 * 用户所属机构及其下属机构所对应的所有spcode
	 * @param userId
	 * @return
	 */
	public String findAllSpcodes(String userId) {
		String spcodeS = "";
		try {
//	        this.init();
				sql="select te.sp_code from t_employee te join t_user tu on te.employee_id=tu.employee_id where user_id='"+userId+"'";
//			prep=conn.prepareStatement(sql);
//			prep.setString(1, userId);
//			rs1=prep.executeQuery();
			String spcode = this.getOneString();
//			if(rs1.next()){
//				spcode=rs1.getString(1);
//			}
				sql="select t.spcode,t.upspcode from t_spcode t";
			//prep=conn.prepareStatement(sql);	
			//rs1=prep.executeQuery();
			List<TSpcode> tslist = new ArrayList<TSpcode>();
			TSpcode t_spcode = null;
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
//			prep=conn.prepareStatement(sql);
//			rs1=prep.executeQuery();
//			while(rs1.next()){
			for(Object[]o:temp)
			{
				t_spcode=new TSpcode();
				t_spcode.setSpcode(this.getString(o[0]));
				t_spcode.setUpspcode(this.getString(o[1]));
				tslist.add(t_spcode);
			}	
			List<String> spcode_templist=new ArrayList<String>();
			spcode_templist = getSpcode_templist(tslist,spcode,spcode_templist);
			if(spcode_templist.size()>0){
	        	for(String spcode_temp:spcode_templist){
	        		spcodeS+="'"+spcode_temp+"',";
	        	}
	        	if(!"".equals(spcodeS)){
	        		spcodeS=spcodeS.substring(0, spcodeS.length()-1);
	        	}
			}
			
		 }catch (Exception e) {
				logger.error("findAllSpcodes fail !!!" + e.getMessage());
			
				}
		if("".equals(spcodeS)){
			spcodeS="''";
		}
		return spcodeS;
	}
	
	private List<String> getSpcode_templist(List<TSpcode> tslist,String spcode_temp,List<String> spcode_templist){
		spcode_templist.add(spcode_temp);
		for(TSpcode ts:tslist){
			if(spcode_temp.equals(ts.getUpspcode())){
				getSpcode_templist(tslist,ts.getSpcode(),spcode_templist);
			}
		}
		return spcode_templist;
	}

	/**
	 * 用户所属机构及其下属机构所对应的所有userid
	 * @param userId
	 * @return
	 */
	public String findAllUserids(String spcodeS) {
		String userids = "";
		try {
	       // this.init();
			sql="select tu.user_id from t_employee te join t_user tu on te.employee_id=tu.employee_id where te.sp_code in ("+spcodeS+")";
		//	rs1=stmt.executeQuery(sql);
			//String userid=null;
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
//			prep=conn.prepareStatement(sql);
//			rs1=prep.executeQuery();
//			while(rs1.next()){
			for(Object[]o:temp)
			{
				//userid=rs1.getString(1);
				userids+="'"+this.getString(o[0])+"',";
			}
			if(!"".equals(userids)){
				userids=userids.substring(0, userids.length()-1);
        	}
			//System.out.println("userids:"+userids);
			//this.closeAll();
		 }catch (Exception e) {
				logger.error("findAllUserids fail !!!" + e.getMessage());
				e.printStackTrace();
				
		}
		if("".equals(userids)){
			userids="''";
		}
		return userids;
	}
	
	 public static boolean StringIsNull(Object str) {
	        boolean b = false;
	        try {
	            if (!str.equals("") && !str.equals("null")) {
	                b = true;
	            }
	        } catch (Exception e) {
	        }
	        return b;
	    }
	 
	    public static boolean StringIsNotNull(Object str) {
	        boolean b = false;
	        try {
	            if (!str.equals("") && !str.equals("null")) {
	                b = true;
	            }
	        } catch (Exception e) {
	        }
	        return b;
	    }

	//add for database optimization
	public String getCidStrForContentidGLobal(String contentidstr) 
	{
		String findSql = "SELECT cid FROM news_contenttitle where contentid in ("+contentidstr+")";
		String result = "";
		try {
		//	ArrayList<DataMap> cidList = this.queryALLResultSet(findSql.toString());
			Object temp=this.em.createNativeQuery(findSql).getResultList();
//			prep=conn.prepareStatement(sql);
//			rs1=prep.executeQuery();
//			while(rs1.next()){
			
	        	
	        	/*  513 */       if ((temp instanceof ArrayList))
	        	/*      */       {
	        	/*  515 */         List tempp = (List)temp;
	        	/*  516 */         for (int a = 0; a < tempp.size(); a++)
	        	/*      */         {
	        	/*  518 */           Object o = tempp.get(a);
	        	/*  519 */         	result += this.getString(o.toString())+",";
	        	                   }
	        	                 }else
	        	                		result += this.getString(temp.toString())+",";
	        	/*  520 */          
	        	/*      */ 
	        	/*  541 */          
			
			
//			for(Object[]o:temp)
//			{
//				//userid=rs1.getString(1);
////				userids+="'"+this.getString(o[0])+"',";
////				String cid = (String)tmp.get("cid");
//				result += this.getString(o[0])+",";
//			}
			if(!"".equals(result))
				result = result.substring(0,result.length()-1);
		} catch (Exception e) {
			logger.error(" getCidStrForContentidGLobal fail !!!" +e.getMessage());
		}
		return result;
	}
	
	

	public void setObject(Object parent,Object child){
		
			// Class c = parent.getClass();
			Method[]m= parent.getClass().getMethods();
			Method[]mc= child.getClass().getMethods();
			//child.getClass().getf
			String temp,tempc,field;
			for(Method mm:m)
			{
				temp=mm.getName();
				if(temp.startsWith("get")&&!"getClass".equals(temp))
				{
					field=temp.substring(3);
				//	System.out.println("--------"+field);
					for(Method mmc:mc)
					{
						tempc=mmc.getName();
					//	System.out.println("--22222222222222222-----"+tempc);
						if(tempc.startsWith("set") && field.equals(tempc.substring(3)))
						{
							 Object o=null;
							try {
								mmc.invoke(child, mm.invoke(parent, null));
							} catch (Exception e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} 
							break;
						}
						
					}
					
					
					
					
					//if(o==null)continue;
					
					
//					
//					temp=o.toString();
//					if(!"".equals(temp)){
//						hql.append(pre+field+" = ?");
//						parameters.add(o);
//					}
					
				}
			}
	}
	
	public void setparam(Object dto,StringBuffer hql,List<Object> parameters,String pre){
	
	if(dto != null){
		 Class c = dto.getClass();
		Method[]m= c.getMethods();
		String temp,field;
		for(Method mm:m)
		{
			temp=mm.getName();
			if(temp.startsWith("get")&&!"getClass".equals(temp))
			{
				Object o=null;
				try {
					o = mm.invoke(dto, null);
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				if(o==null)continue;
				field=temp.substring(3,4).toLowerCase()+temp.substring(4);
				
				temp=o.toString();
				if(!"".equals(temp)){
					hql.append(pre+field+" = ?");
					parameters.add(o);
				}
				
			}
		}
		
	}
	}
	
	public String setparam(Object dto,String hql,String pre){
		
		if(dto != null){
			 Class c = dto.getClass();
			Method[]m= c.getMethods();
			String temp,field;
			for(Method mm:m)
			{
				temp=mm.getName();
				if(temp.startsWith("get")&&!"getClass".equals(temp))
				{
					Object o=null;
					try {
						o = mm.invoke(dto, null);
					} catch (IllegalArgumentException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					if(o==null)continue;
					field=temp.substring(3,4).toLowerCase()+temp.substring(4);
					
					temp=o.toString();
					if(!"".equals(temp)){
						hql+=(" and "+pre+field+" = '"+temp+"' ");
						//parameters.add(o);
					}
					
				}
			}
			
		}
		return hql;
	}
	
	public boolean setFieldValue(Object dto,String fname,String value) throws Exception{
		
		if(dto != null){
			Method[]m= dto.getClass().getMethods();
			String temp,field,fnametemp;
			//fnametemp=fname.toLowerCase();
			fnametemp=fname.replaceAll("_", "");
			for(Method mm:m)
			{
				temp=mm.getName();
				if(temp.startsWith("set"))
				{
					field=temp.substring(3).toLowerCase();
					if(field.equals(fnametemp))
						{
						Class ccc=  mm.getParameterTypes()[0];
						if(ccc.getSimpleName().equals("String"))  mm.invoke(dto, value);
						else if(ccc.getSimpleName().equals("Integer"))  mm.invoke(dto, Integer.valueOf(value));
						else if(ccc.getSimpleName().equals("Double"))  mm.invoke(dto, Double.valueOf(value));
						else if(ccc.getSimpleName().equals("Long"))  mm.invoke(dto, Long.valueOf(value));
						else if(ccc.getSimpleName().equals("Date"))
							{
							    if(value.length()>11)
							    	mm.invoke(dto, MyServer.sdf.parse(value));
							    else
							        mm.invoke(dto, MyServer.sdfsample.parse(value));
							}
							return true;
						}
					//field=temp.substring(3,4).toLowerCase()+temp.substring(4);
//					Object o=null;
//					try {
//						o = mm.invoke(dto, null);
//					} catch (IllegalArgumentException e) {
//						// TODO Auto-generated catch block
//						e.printStackTrace();
//					} catch (IllegalAccessException e) {
//						// TODO Auto-generated catch block
//						e.printStackTrace();
//					} catch (InvocationTargetException e) {
//						// TODO Auto-generated catch block
//						e.printStackTrace();
//					}
				}
			}
			
		}
		return false;
		//return hql;
	}
	
	public List getObjectList(String hql,Class cs) throws Exception{
		//String hql = "from Brand w order by w.name";
		String tm=hql.toLowerCase();
    	tm=tm.substring(0,tm.indexOf("from"));
    	tm=tm.replace("select", "");
    	tm=tm.replace("distinct", "");
    	String []ss=tm.split(",");
    //	System.out.println(tm);
    	List<String>cn=new ArrayList<String>();
    	for(String s:ss)
    	{
    		String t=s;
    		int i=s.indexOf("as");
    		if(i!=-1)t=t.substring(i+2,t.length());
    		t=t.replaceAll(" ", "");
    		i=t.indexOf(".");
    		if(i!=-1)t=t.substring(i+1,t.length());
//    		System.out.println(t);
//    		System.out.println(s.indexOf("."));
    		cn.add(t);
    	}
    	Object c=null;
    	List<Object>co=new ArrayList<Object>();
		List<Object[]> s=em.createNativeQuery(sql).getResultList();
		for(Object[]o:s)
		{
			c=cs.newInstance();
			for(int i=0;i<o.length;i++)
			{
				if(o[i]!=null)
				{
					   boolean t=setFieldValue(c,cn.get(i),o[i].toString());
					   if(!t)System.out.println("未发现属性："+cn.get(i));
				}
			}
			co.add(c);
			
		}
		return co;
	}
	
	public List getAllList(String hql) throws Exception{
		//String hql = "from Brand w order by w.name";
		return em.createQuery(hql).getResultList();
	}
	@SuppressWarnings("unchecked")
	
	
	public PageJson pagination(int start, int limit, String hql, Object[] parameters) throws Exception{
		
		if(hql.indexOf("from")!=-1)
		{
		   sql=hql.substring(hql.indexOf("from"));
		}else if(hql.indexOf("FROM")!=-1)
		{
			sql=hql.substring(hql.indexOf("FROM"));
			
		}
		sql="select count(*) "+sql;
			 
		Query q1 = em.createQuery(sql);
		if(parameters != null){
			for(int i=0; i<parameters.length; i++){
				q1.setParameter(i+1, parameters[i]);
			}
		}
		long ii=(java.lang.Long)q1.getSingleResult();
		int total=(int)ii;
		System.out.println(ii+"-"+sql);
		
		Query q = em.createQuery(hql);
		if(parameters != null){
			for(int i=0; i<parameters.length; i++){
				q.setParameter(i+1, parameters[i]);
			}
		}
		return this.paginationaa(start, limit, q,total);
	}
	public PageJson paginationaa(int start, int limit, Query q,int total) throws Exception{
		PageJson page = new PageJson();
		if(q != null){
			int totalCount =total;// q.getResultList().size();
			q.setFirstResult(start);
			q.setMaxResults(limit);
			List list = q.getResultList();
			page.setResult(list);
			page.setTotalCount(totalCount);
		}
		return page;
	}
	
	public PageJson paginationNative(int start, int limit, String hql, Object[] parameters) throws Exception{
		Query q = em.createNativeQuery(hql);
		if(parameters != null){
			for(int i=0; i<parameters.length; i++){
				q.setParameter(i+1, parameters[i]);
			}
		}
		
		return this.pagination(start, limit, q);
	}
	
	@SuppressWarnings("unchecked")
	public PageJson pagination(int start, int limit, Query q) throws Exception{
		
		System.out.println("---------");
		PageJson page = new PageJson();
		if(q != null){
			int totalCount = q.getResultList().size();
			q.setFirstResult(start);
			q.setMaxResults(limit);
			List list = q.getResultList();
			page.setResult(list);
			page.setTotalCount(totalCount);
		}
		return page;
	}
	
	public void insert(Object obj) throws Exception{
		em.persist(obj);
		em.flush();
	}
	
	public void modify(Object obj) throws Exception{
		em.merge(obj);
		em.flush();
	}
	public Object findById(String id, Class className) throws Exception{
		return em.find(className, id);
	}
	public Object findById(String id, String className) throws Exception{
		return em.find(Class.forName(className), id);
	}
	public Object findById(Integer id, Class className) throws Exception{
		return em.find(className, id);
	}
	public void delete(String id, String className) throws Exception{
		em.remove(em.find(Class.forName(className), id));
		em.flush();
	}
	
	@SuppressWarnings("unchecked")
	public void delete(String id, Class className) throws Exception{
		em.remove(em.find(className, id));
		em.flush();
	}
	@SuppressWarnings("unchecked")
	public void delete(Integer id, Class className) throws Exception{
		em.remove(em.find(className, id));
		em.flush();
	}
	public void deleteString(String idArr, Class className) throws Exception{
		if(idArr!=null && !"".equals(idArr))
		{
			String[] ids=idArr.split(",");
			for(String s:ids)this.delete(s, className);
		}
	}
	public void deleteInt(String idArr, Class className) throws Exception{
		if(idArr!=null && !"".equals(idArr))
		{
			String[] ids=idArr.split(",");
			for(String s:ids)this.delete(Integer.valueOf(s), className);
		}
	}
}
